/**
 * 
 */
package com.zhiteng.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.zhiteng.entity.ActualNode;
import com.zhiteng.entity.AdjustBill;
import com.zhiteng.entity.CourseNode;
import com.zhiteng.entity.CourseTable;
import com.zhiteng.entity.School;
import com.zhiteng.entity.Teacher;
import com.zhiteng.mapper.ActualNodeMapper;
import com.zhiteng.mapper.CourseNodeMapper;
import com.zhiteng.mapper.CourseTableMapper;
import com.zhiteng.service.CourseTableService;
import com.zhiteng.service.RedisService;
import com.zhiteng.service.SchoolService;
import com.zhiteng.service.TeacherService;
import com.zhiteng.utils.common.DateUtil;
import com.zhiteng.utils.common.IDUtils;
import com.zhiteng.utils.common.PoiUtils;
import com.zhiteng.vo.CourseTableVo;
import com.zhiteng.vo.TeacherVo;

import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.entity.Example.Criteria;

/**  
* <p>Title: CourseTableServiceImpl.java</p>  
* <p>Description:课程表Service </p>  
* @author dragon  
* @date 2018年11月10日  
* @version 1.0  
*/
@Service
public class CourseTableServiceImpl implements CourseTableService {
	@Autowired
	private CourseNodeMapper courseNodeMapper;
	@Autowired
	private CourseTableMapper courseTableMapper;
	@Autowired
	private RedisService redisService;

	@Autowired
	private TeacherService teacherService;
	@Autowired
	private SchoolService schoolService;
	@Autowired
	private ActualNodeMapper actualNodeMapper;
	
	//	上传总课表，解析生成班级表、教师表、总任课表、总课表
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Map<String,Object> saveCourseTableAll(MultipartFile file, String schoolId, String year, String term) {
		//	上传之前需要将之前的课表删除，防止重复上传（同一学校）：同一学校数据库中一次只保存一学期的课表
		deleteOldCourseTable(schoolId);
		
		Map<String,Object> rm = new HashMap<>();	//	用来封装返回结果
		rm.put("code", -1);	//	初始化
		rm.put("data", null); //	初始化
		
		try {
			// 0、根据传入的Excel生成单元格实体、班级表、总课表，返回一个Map，包含班级表和总课表的List集合、教师姓名的set集合、该校每天上几节课、每周上几天课、学年名称、学期名称
			Map<String, Object> resultMap = queryCourseNodesByExcel(file, schoolId, year,term);
			
			// 1、先保存总课表和班级表到数据库
			List<CourseTable> tableList = (List<CourseTable>) resultMap.get("tableList");
			saveCourseTableBatch(tableList);

			// 2、通过查询生成所有的教师课表以及总任课表并保存到数据库
			// 2.1、查询总课表中的所有教师名称
			Set<String> teacherNameSet = (Set<String>) resultMap.get("teacherNameSet"); // 总课表中教师姓名集合
			// 2.2、根据教师名称查询后生成所有教师个人课表及总任课表的List
			List<CourseTable> teacherTableList = queryAllTeacherCourseTable(schoolId, resultMap,teacherNameSet);
			saveTeacherTableBatch(teacherTableList);
			
			// 3、将总课表和总任课表缓存到redis
			saveTotalTables2Redis(schoolId);

			// 4、教师名称与腾讯数据（tb_teacher表）进行匹配，得到教师id并插入到table表和node表
			List<String> unmatchedTeacherNames = updateTeacherIds(schoolId, teacherNameSet);

			if (unmatchedTeacherNames.isEmpty()) {
				rm.put("code",0);		//return ZTResult.result(0, "课表上传成功");
			} else {
				rm.put("code",1);    //return ZTResult.result(0, "课表上传成功，但以下教师未匹配成功", unmatchedTeacherNames);
				rm.put("data",unmatchedTeacherNames);
			}

		} catch (Exception e) {
			e.printStackTrace();
			rm.put("code",2);  //return ZTResult.result(1, "课表上传失败，请检查上传的excel文件！");
			//	出现异常，手动回滚（否则不会回滚）
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();   
		}
		return rm;
	}

	//	上传总课表后将总课表和总任课表缓存到Redis
	@SuppressWarnings("rawtypes")
	private void saveTotalTables2Redis(String schoolId) {
		CourseTableVo courseTableVo = new CourseTableVo();
		courseTableVo.setSchool_id(schoolId);
		courseTableVo.setType(4);
		CourseTable totalTeacherTable = queryTotalTeacherTable(courseTableVo,null);	//	总任课表
		courseTableVo.setType(3);
		CourseTable totalTable = queryTotalCourseTable(courseTableVo,null);	//	总课表
		
		redisService.hset(schoolId, "totalTeacherTable", JSON.toJSONString(totalTeacherTable));
		redisService.hset(schoolId, "totalTable", JSON.toJSONString(totalTable));
	}

	//	根据传入的Excel生成单元格实体、班级表、总课表，返回一个Map，包含班级表和总课表的List集合、教师姓名的set集合、该校每天上几节课、每周上几天课、学年、学期
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private Map<String,Object> queryCourseNodesByExcel(MultipartFile file, String schoolId, String year_name,String term_name) throws Exception{
			
			POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
			HSSFWorkbook wb=new HSSFWorkbook(fs);
			HSSFSheet hssfSheet=wb.getSheetAt(0);  	//	获取第一个Sheet页
			
			//	1、需要返回的班级课程表和总课程表的集合
			List<CourseTable> tableList = new ArrayList<>();
			//	2、需要返回总课程表中无重复的教师的集合
			Set<String> teacherNameSet = new HashSet<>();
			
			Integer totalNode=null;
			Integer totalDay=null;
			if (hssfSheet != null) {
				//	获取总列数
				int coloumNum = hssfSheet.getRow(1).getPhysicalNumberOfCells();
				//	获取最后一行的索引，即比行总数小1
				//	       注意：这里有个坑，生成的课程表做任何修改再保存后，rowNum的值都比实际大1，为什么？如何解决？（多加个判断：cell是否为null）
				int rowNum = hssfSheet.getLastRowNum();
				System.out.println(rowNum);
				//	计算每天上几节课
				totalNode = getTotalNodeBySheet(hssfSheet);
				//	计算每星期上几天课
				totalDay = (coloumNum-2)/totalNode;
				//System.out.println("每星期上"+totalDay+"天课！");
				
				//	总课程表
				CourseTable totalTable = new CourseTable();
				totalTable.setType(3); 	//	总课表
				totalTable.setId(IDUtils.getUUID());
				totalTable.setSchool_id(schoolId);
				totalTable.setYear_name(year_name);
				totalTable.setTerm_name(term_name);
				totalTable.setRownum(rowNum+1);
				totalTable.setColnum(coloumNum);
				totalTable.setTotal_day(totalDay);
				totalTable.setTotal_node(totalNode);
				StringBuffer table_ids_sb = new StringBuffer();	//	总课表子节点id(班级表的id)拼接字符串
				
				//	遍历第三行开始的每一行的每一个单元格
				for(int i=2;i<=rowNum;i++) { //	从第三行开始，每一行是一个班级表
					
					if(hssfSheet.getRow(i)==null) continue;	//	跳过该行
					
					//	班级课程表
					CourseTable courseTable = new CourseTable();
					courseTable.setType(1); //	班级表
					
					courseTable.setRownum(totalNode+1);
					courseTable.setColnum(totalDay+1);
					String tableId = IDUtils.getUUID();
					courseTable.setId(tableId);
					
					table_ids_sb.append(tableId).append(",");
					
					courseTable.setYear_name(year_name);
					courseTable.setTerm_name(term_name);
					courseTable.setTotal_node(totalNode);
					courseTable.setTotal_day(totalDay);
					courseTable.setSchool_id(schoolId);;
					
					//	获取年级名称
					HSSFCell c= hssfSheet.getRow(i).getCell(0);
					String  gradeName = null;
					if(c!=null) {	//	解决生成的课程表做任何修改再保存后会出现rowNum的值都比实际大1的问题
						gradeName = PoiUtils.getValue(c);;
					}else {	
						break;
					}
					courseTable.setGrade_name(gradeName);
					
					//	获取班级名称
					String className = PoiUtils.getValue(hssfSheet.getRow(i).getCell(1));
					courseTable.setClass_name(className);
					
					StringBuffer node_ids_sb = new StringBuffer();	//	班级课表子节点id（node的id）拼接字符串
					List<CourseNode> nList = new ArrayList<>();
					// 	weekStr用于暂存星期几
					String week = null;
					for(int j=2;j<=coloumNum-1;j++) { 	//	从第三列开始
						String weekStr = PoiUtils.getValue(hssfSheet.getRow(0).getCell(j));
						//	如果能读到星期几，就用读到的去更新weekStr；如果读不到，就不更新weekStr，用之前的weekStr
						if(StringUtils.isNotBlank(weekStr)) {
							week = weekStr;
						}
						
						//	获取i行j列的单元格
						if(hssfSheet.getRow(i).getCell(j)!=null && StringUtils.isNotBlank(PoiUtils.getValue(hssfSheet.getRow(i).getCell(j)))) {	//	不是空单元格
							HSSFCell cell = hssfSheet.getRow(i).getCell(j);
							//	创建单元格实体
							CourseNode courseNode = new CourseNode();
							String nodeId = IDUtils.getUUID();
							node_ids_sb.append(nodeId).append(",");
							courseNode.setId(nodeId);
							courseNode.setGrade_name(gradeName);
							courseNode.setClass_name(className);
							courseNode.setYear_name(year_name);
							courseNode.setTerm_name(term_name);
							courseNode.setCourse_table_id(tableId);
							courseNode.setSchool_id(schoolId);;
							//	单元格属于哪一个班级表
							
							//	单元格坐标
							//	第几节课
							String courseNumStr = PoiUtils.getValue(hssfSheet.getRow(1).getCell(j));
							courseNode.setCourse_num((new Double(Double.parseDouble(courseNumStr))).intValue());
							//	星期几
							courseNode.setWeek(week);
							
							//	获取单元格中的字符串
							String str = PoiUtils.getValue(cell);
							//	按换行符切割字符串
							String[] splitStr = str.split("\\r?\\n");
							//	设置课程名称
							if(splitStr.length>0) courseNode.setCourse_name(splitStr[0]);
							//	设置任课老师姓名
							if(splitStr.length>1) {
								courseNode.setTeacher_name(splitStr[1]);
								//	将老师姓名添加到set集合中
								teacherNameSet.add(splitStr[1]);
							}
							
							//	设置上课地址
							if(splitStr.length>2) courseNode.setAddress(splitStr[2]);
							
							nList.add(courseNode);
						}
					}
					//	内循环结束
					courseTable.setNodeList(nList);
					//	去掉node_ids最后一个逗号
					String node_ids = node_ids_sb.substring(0, node_ids_sb.length()-1);
					courseTable.setNode_ids(node_ids);
					
					tableList.add(courseTable);
				}
				//	外循环结束，去掉最后一个逗号
				String table_ids = table_ids_sb.substring(0, table_ids_sb.length()-1);
				totalTable.setNode_ids(table_ids);
				tableList.add(totalTable);
			}
			wb.close();
			
			//	需要返回的从Excel总课表中解析到的数据
			Map<String,Object> resultMap = new HashMap<>();
			resultMap.put("tableList", tableList);
			resultMap.put("teacherNameSet", teacherNameSet);
			resultMap.put("totalNode", totalNode);
			resultMap.put("totalDay", totalDay);
			resultMap.put("yearName", year_name);
			resultMap.put("termName", term_name);
			
			return resultMap;
	}
	
	//	根据总课表的sheet页计算每天有几节课并返回
	private int getTotalNodeBySheet(HSSFSheet hssfSheet) {
		/*
		 * 	计算每天有几节课：
		 * 		思路：每张总课表第二行第三列单元格的内容是“1”（第一节课），该行往后，第i列再次出现“1”
		 * 	  	则i-2就是每天上课的节数
		 */
		int totalNode = 1;
		HSSFCell c1 = hssfSheet.getRow(1).getCell(2);
		String str = PoiUtils.getValue(c1);
		for(int i=3;i<15;i++) {	//	每天不可能超过15节课
			HSSFCell c2 = hssfSheet.getRow(1).getCell(i);
			if(PoiUtils.getValue(c2).equals(str)) {
				totalNode = i-2;
				break;
			}
		}
		//System.out.println("每天上"+totalNode+"节课！");
		return totalNode;
	}
	
	

	@SuppressWarnings({ "unchecked", "rawtypes" })
	//	保存一张总课表和其包含的所有班级表
	public void saveCourseTableBatch(List<CourseTable> courseTableList) {
		for (CourseTable courseTable : courseTableList) {
			courseTableMapper.insertSelective(courseTable);
			if(courseTable.getType()==1) {	//	班级表
				List<CourseNode> nodeList = courseTable.getNodeList();
				for (CourseNode courseNode : nodeList) {
					courseNodeMapper.insertSelective(courseNode);
				}
			}
		}
	}

	@Override
	public List<String> queryCourseNameBySchool(String schoolName) {
		List<String> courseNameList = courseNodeMapper.queryCourseNameBySchool(schoolName);
		return courseNameList;
	}

	@Override
	public List<String> queryTeacherNameBySchoolAndCourse(String schoolName, String courseName) {
		List<String> teacherNameList = courseNodeMapper.queryTeacherNameBySchoolAndCourse(schoolName, courseName);
		return teacherNameList;
	}

	@Override
	public List<String> queryGradeBySchool(String schoolId) {
		List<String> gradeList = courseTableMapper.queryGradeBySchool(schoolId);
		return gradeList;
	}

	@Override
	public List<String> queryClassBySchoolAndGrade(String schoolId, String gradeName) {
		List<String> classList = courseTableMapper.queryClassBySchoolAndGrade(schoolId, gradeName);
		return classList;
	}

	//@Override
	//	根据学校名称和教师名称计算该教师一周中的每一天各有几节课
	public Map<Integer, Integer> queryCountNodeEveryDay(String schoolId, String teacherId) {
		
		Map<Integer,Integer> map = new HashMap<>();
		
		//	查询指定老师一周中的每天分别有多少节课
		Integer a = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期一");
		Integer b = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期二");
		Integer c = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期三");
		Integer d = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期四");
		Integer e = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期五");
		Integer f = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期六");
		Integer g = courseNodeMapper.queryTotalCourseByWeek(schoolId, teacherId, "星期日");
	
		
		map.put(1, a);
		map.put(2, b);
		map.put(3, c);
		map.put(4, d);
		map.put(5, e);
		map.put(6, f);
		map.put(7, g);
		
		//键0用来判断教师有没有课或者教师不存在
		if(a+b+c+d+e+f+g==0) {	//	该教师没有课（教师id错误/学校id错误）
			map.put(0, -1);
		}else {
			map.put(0, 0);
		}
		return map;
	}

	@SuppressWarnings("rawtypes")
	//	批量保存教师表和总任课表
	private void saveTeacherTableBatch(List<CourseTable> tableList) {
		for (CourseTable courseTable : tableList) {
			courseTableMapper.insertSelective(courseTable);
		}
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	//	生成所有教师的教师个人课表以及总任课表
	private List<CourseTable> queryAllTeacherCourseTable(String schoolId,Map<String,Object> resultMap,Set<String> teacherNameSet) {
		//	用来存放教师个人课表和总任课表
		List<CourseTable> tableList = new ArrayList<>();
		
		CourseTable<CourseTable> teacherTotalTable = new CourseTable<>();	//	总任课表
		teacherTotalTable.setType(4);	//	总任课表
		teacherTotalTable.setId(IDUtils.getUUID());
		teacherTotalTable.setSchool_id(schoolId);
		
		//	查询该学校每周上几天课，每天上几节课（total_node,total_day）
		Integer totalNode = (int)resultMap.get("totalNode"); 
		Integer totalDay = (int)resultMap.get("totalDay"); 
		teacherTotalTable.setColnum(totalNode*totalDay+1);	//	列数
		teacherTotalTable.setRownum(teacherNameSet.size()+2);	//	行数
		
		teacherTotalTable.setYear_name((String)resultMap.get("yearName"));
		teacherTotalTable.setTerm_name((String)resultMap.get("termName"));
		teacherTotalTable.setTotal_day((int)resultMap.get("totalDay"));
		teacherTotalTable.setTotal_node((int)resultMap.get("totalNode"));
		
		//	拼接总任课表node_ids
		StringBuffer table_ids_sb = new StringBuffer();
		for (String teacherName : teacherNameSet) { 
			CourseTable<CourseNode> courseTable = queryTeacherCourseTable(schoolId,teacherName,resultMap);	//	教师个人课表
			tableList.add(courseTable);	//	教师表添加到集合中
			table_ids_sb.append(courseTable.getId()).append(",");
			teacherTotalTable.getNodeList().add(courseTable);
		}
		//	去掉node_ids最后一个逗号
		String node_ids = table_ids_sb.substring(0, table_ids_sb.length()-1);
		teacherTotalTable.setNode_ids(node_ids);
		tableList.add(teacherTotalTable);
		
		return tableList;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	//	查询生成指定教师名称的教师个人课表
	private CourseTable queryTeacherCourseTable(String schoolId,String teacherName,Map<String,Object> resultMap) {
		CourseTable courseTable = new CourseTable();
		CourseNode courseNode = new CourseNode();
		courseNode.setSchool_id(schoolId);
		courseNode.setTeacher_name(teacherName);
		List<CourseNode> nodeList = courseNodeMapper.select(courseNode);
		
		//	拼接node_ids
		StringBuffer node_ids_sb = new StringBuffer();
		for (CourseNode node : nodeList) {
			node_ids_sb.append(node.getId()).append(",");
		}
		//	去掉node_ids最后一个逗号
		String node_ids = node_ids_sb.substring(0, node_ids_sb.length()-1);
		
		courseTable.setNodeList(nodeList);
		courseTable.setSchool_id(schoolId);
		courseTable.setType(2);	// 教师表
		courseTable.setId(IDUtils.getUUID());
		courseTable.setNode_ids(node_ids);
		courseTable.setTeacher_name(teacherName);
		
		//	查询该学校每周上几天课，每天上几节课（total_node,total_day）
		Integer totalNode = (int)resultMap.get("totalNode"); 
		Integer totalDay = (int)resultMap.get("totalDay"); 
		courseTable.setTotal_node(totalNode);
		courseTable.setTotal_day(totalDay);
		courseTable.setRownum(totalNode+1);
		courseTable.setColnum(totalDay+1);
		courseTable.setYear_name((String)resultMap.get("yearName"));
		courseTable.setTerm_name((String)resultMap.get("termName"));
		
		return courseTable;
	}

	private List<String> updateTeacherIds(String schoolId, Set<String> teacherNameSet) {
		School school = schoolService.querySchoolById(schoolId);
		List<String>  unmatchedTeacherNames = new ArrayList<>();	//	用来存放未匹配到的教师姓名
		for (String teacherName : teacherNameSet) {
			//	ps:teacherNameSet中的teacherName是课程表中的老师姓名，如有两个张三，实际会是张三A、张三B之类的区分
			//		--	因此queryTeacherByTeacherNameAndSchoolName查询到的最多只有一条记录
			List<Teacher> teacherList = teacherService.queryTeacherByTeacherNameAndSchoolName(school.getName(),teacherName);
			if(teacherList==null || teacherList.isEmpty()) {	//	未匹配到
				unmatchedTeacherNames.add(teacherName);
			}else {	//	匹配到
				String teacherId = teacherList.get(0).getUserid();
				//	将教师id更新到tb_course_table、tb_course_node表中对应教师名称的记录上
				updateTeacherId(schoolId,teacherId,teacherName);
			}
		}
		return unmatchedTeacherNames;
	}

	@SuppressWarnings("rawtypes")
	private void updateTeacherId(String schoolId, String teacherId, String teacherName) {
		
//		更新tb_course_node表
		CourseNode courseNode = new CourseNode();
		courseNode.setTeacher_id(teacherId);
		Example example = new Example(CourseNode.class);
		Criteria criteria = example.createCriteria();
		criteria.andEqualTo("teacher_name", teacherName);
		courseNodeMapper.updateByExampleSelective(courseNode, example);
		
//		更新tb_course_table表
		CourseTable courseTable = new CourseTable();
		courseTable.setTeacherId(teacherId);;
		Example example2 = new Example(CourseTable.class);
		Criteria criteria2 = example2.createCriteria();
		criteria2.andEqualTo("teacher_name", teacherName);
		criteria2.andEqualTo("type", 2);
		courseTableMapper.updateByExampleSelective(courseTable, example2);
		
	}
	
	//	 根据学校id查询上传课程表过程中未匹配到的教师
	@Override
	public List<String> queryUnmatchedTeacherNamesBySchoolId(String schoolId) {
		List<String> unmatchedTeacherNames = courseTableMapper.queryUnmatchedTeacherNamesBySchoolId(schoolId);
		return null;
	}
	
	
	//	查询课程表
	@SuppressWarnings({ "rawtypes"})
	@Override
	public CourseTable queryCourseTable(CourseTableVo courseTableVo) throws Exception {
		//	要查询的周次
		Integer weeksInYear = courseTableVo.getWeeks();	
		if(weeksInYear==null || weeksInYear==0) {
			//	计算出调用接口时间对应的日期在一年中的周次
			weeksInYear = DateUtil.getWeek(DateUtil.Date2String(new Date()));
		}
		
		CourseTable courseTable = null; //	用于返回课程表
		int type = courseTableVo.getType();
		if(type==1) {		//	班级表
			courseTable = queryClassCourseTable(courseTableVo,weeksInYear);
		}else if(type==2) {	//	教师表
			courseTable = queryTeacherCourseTable(courseTableVo,weeksInYear);
		}else if(type==3) {	//	总课表
			courseTable = queryTotalCourseTable(courseTableVo,weeksInYear);
		}else {				//	总任课表
			courseTable = queryTotalTeacherTable(courseTableVo,weeksInYear);
		}
		
		return courseTable;
	}
	
	//	查询总任课表（不包括调代课的）
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private CourseTable queryTotalTeacherTable(CourseTableVo courseTableVo, Integer weeksInYear) {
		//	从redis缓存中取，取不到才去数据库中查找
		String totalTeacherTable = redisService.hget(courseTableVo.getSchool_id(), "totalTeacherTable");
		if(StringUtils.isNotBlank(totalTeacherTable)) {
			return JSON.parseObject(totalTeacherTable, CourseTable.class);
		}
		
		//	查找总任课表
		CourseTable recordTable = new CourseTable();
		recordTable.setSchool_id(courseTableVo.getSchool_id());;
		recordTable.setType(4);
		CourseTable courseTable = courseTableMapper.selectOne(recordTable);
		if(courseTable==null) return null;
		
		String tableIds = courseTable.getNode_ids();	//	教师表的id集合
		List<CourseTable> tList = new ArrayList<>();	//	用来存放所有教师表
		for (String tableId : tableIds.split(",")) {
			CourseTable table = courseTableMapper.selectByPrimaryKey(tableId);
			if(table==null) break;
			queryNodeList(table);
			//	不需要返回的设置成null
			table.setId(null);
			table.setNode_ids(null);
			table.setSchool_id(null);
			table.setYear_name(null);
			table.setTerm_name(null);
			table.setTotal_node(null);
			table.setTotal_day(null);
			table.setRownum(null);
			table.setColnum(null);
			
			tList.add(table);
		}
		courseTable.setNodeList(tList);
		courseTable.setNode_ids(null);
		courseTable.setId(null);
		courseTable.setSchool_id(null);
		//	放入一份到缓存
		redisService.hset(courseTableVo.getSchool_id(), "totalTeacherTable", JSON.toJSONString(courseTable));
		return courseTable;
	}
	
	//	查询总课表（不包括调代课的）
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private CourseTable queryTotalCourseTable(CourseTableVo courseTableVo, Integer weeksInYear) {
		//	从redis缓存中取，取不到才去数据库中查找
		String totalTable = redisService.hget(courseTableVo.getSchool_id(), "totalTable");
		if(StringUtils.isNotBlank(totalTable)) {
			return JSON.parseObject(totalTable, CourseTable.class);
		}
		
		//	查找总课表
		CourseTable recordTable = new CourseTable();
		recordTable.setSchool_id(courseTableVo.getSchool_id());
		recordTable.setType(3);
		CourseTable courseTable = courseTableMapper.selectOne(recordTable);
		if(courseTable==null) return null;
		
		String tableIds = courseTable.getNode_ids();	//	班级表的id集合
		List<CourseTable> tList = new ArrayList<>();	//	用来存放所有班级表
		for (String tableId : tableIds.split(",")) {
			CourseTable table = courseTableMapper.selectByPrimaryKey(tableId);
			if(table==null) break;
			queryNodeList(table);
			//	不需要返回的设置成null
			table.setId(null);
			table.setNode_ids(null);
			table.setSchool_id(null);
			table.setYear_name(null);
			table.setTerm_name(null);
			table.setTotal_node(null);
			table.setTotal_day(null);
			table.setRownum(null);
			table.setColnum(null);
			
			tList.add(table);
		}
		courseTable.setNodeList(tList);
		courseTable.setNode_ids(null);
		courseTable.setId(null);
		courseTable.setSchool_id(null);
		
		//	放入一份到缓存
		redisService.hset(courseTableVo.getSchool_id(), "totalTable", JSON.toJSONString(courseTable));
		
		return courseTable;
	}
	
	//	根据班级表或教师表，查询并添加其所有的单元格
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private void queryNodeList(CourseTable courseTable) {
		String[] nodeIds = courseTable.getNode_ids().split(",");
		List<CourseNode> nodeList = new ArrayList<>();
		for (String nodeId : nodeIds) {
			CourseNode courseNode= courseNodeMapper.selectByPrimaryKey(nodeId);
			//	不需要返回的设置为null
			courseNode.setYear_name(null);
			courseNode.setTerm_name(null);
			//courseNode.setId(null);
			courseNode.setSchool_id(null);
			courseNode.setTeacher_id(null);
			courseNode.setCourse_table_id(null);
			courseNode.setGrade_name(null);
			courseNode.setClass_name(null);
			
			courseNode.setId(null);
			
			nodeList.add(courseNode);
		}
		courseTable.setNodeList(nodeList);
	}

	//	查询教师表(实际)
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private CourseTable queryTeacherCourseTable(CourseTableVo courseTableVo, Integer weeksInYear) {
		//	1、查找教师表
		CourseTable recordTable = new CourseTable();
		recordTable.setSchool_id(courseTableVo.getSchool_id());
		recordTable.setType(2);
		recordTable.setTeacherId(courseTableVo.getTeacherId());
		CourseTable courseTable = courseTableMapper.selectOne(recordTable);
		if(courseTable==null) return null;
		
		//	2、查找实际的单元格节点
		//	根据周次查找出该教师表实际的单元格节点
		ActualNode recordNode = new ActualNode();
		recordNode.setSchoolId(courseTableVo.getSchool_id());
		recordNode.setTeacherId(courseTableVo.getTeacherId());
		recordNode.setWeekly_times(weeksInYear);
		List<ActualNode> actualNodeList = actualNodeMapper.select(recordNode);
		
		//	3、将单元格节点组装到教师表上
		//	教师表的node_ids为单元格id
		String[] nodeIds = courseTable.getNode_ids().split(",");
		List<CourseNode> nodeList = new ArrayList<>();	//	用来保存所有的单元格
		if(actualNodeList!=null && !actualNodeList.isEmpty()) {
			List<CourseNode> actualCourseNodeList = actualNodes2CourseNodes(actualNodeList);	//	实际的单元格节点
			for (String nodeId : nodeIds){
				CourseNode courseNode= courseNodeMapper.selectByPrimaryKey(nodeId);
				boolean flag = false;
				for (CourseNode actualCourseNode : actualCourseNodeList)  {
					//	实际节点中有
					if( actualCourseNode.getCourse_num() == courseNode.getCourse_num() && courseNode.getWeek().equals(actualCourseNode.getWeek())) {
						nodeList.add(actualCourseNode);
						flag = true;
						break;
					}
				}
				if(!flag) {	//	实际节点中没有
					//	不需要返回的设置为null
					courseNode.setId(null);
					courseNode.setYear_name(null);
					courseNode.setTerm_name(null);
					courseNode.setSchool_id(null);
					courseNode.setTeacher_id(null);
					courseNode.setCourse_table_id(null);
					courseNode.setWeek_type(null);
					nodeList.add(courseNode);
				}
			}
		}else {
			for (String nodeId : nodeIds){
				CourseNode courseNode= courseNodeMapper.selectByPrimaryKey(nodeId);
				//	不需要返回的设置为null
				courseNode.setId(null);
				courseNode.setYear_name(null);
				courseNode.setTerm_name(null);
				courseNode.setSchool_id(null);
				courseNode.setTeacher_id(null);
				courseNode.setCourse_table_id(null);
				courseNode.setWeek_type(null);
				nodeList.add(courseNode);
			}
		}
		
		courseTable.setNodeList(nodeList);
		courseTable.setId(null);
		courseTable.setNode_ids(null);

		return courseTable;
	}
	
	//	将ActualNode集合封装成CourseNode集合
	private List<CourseNode> actualNodes2CourseNodes(List<ActualNode> actualNodeList) {
		List<CourseNode> actualCourseNodeList = new ArrayList<>();
		for (ActualNode actualNode : actualNodeList) {
			CourseNode node = new CourseNode();
			node.setBill_id(actualNode.getBill_id());
			node.setCourse_name(actualNode.getCourseName());
			node.setCourse_num(actualNode.getCourseNum());
			node.setWeek(actualNode.getWeek());
			node.setTeacher_name(actualNode.getTeacherName());
			node.setClass_name(actualNode.getClassName());
			node.setType(actualNode.getType2());
			node.setTeacher_id(actualNode.getTeacherId());
			node.setClass_name(actualNode.getClassName());
			node.setGrade_name(actualNode.getGradeName());
			actualCourseNodeList.add(node);
		}
		return actualCourseNodeList;
	}

	//	查询班级表(实际)
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private CourseTable queryClassCourseTable(CourseTableVo courseTableVo, Integer weeksInYear) {
		//	1、查找班级表
		CourseTable recordTable = new CourseTable();
		recordTable.setSchool_id(courseTableVo.getSchool_id());
		recordTable.setType(1);
		recordTable.setGrade_name(courseTableVo.getGrade_name());
		recordTable.setClass_name(courseTableVo.getClass_name());
		CourseTable courseTable = courseTableMapper.selectOne(recordTable);
		if(courseTable==null) return null;
		
		//	2、查找实际的单元格节点
		//	根据周次查找出该班级课表实际的单元格节点
		ActualNode recordNode = new ActualNode();
		recordNode.setSchoolId(courseTableVo.getSchool_id());
		recordNode.setGradeName(courseTableVo.getGrade_name());
		recordNode.setClassName(courseTableVo.getClass_name());
		recordNode.setWeekly_times(weeksInYear);
		List<ActualNode> actualNodeList = actualNodeMapper.select(recordNode);
		
		//	3、将单元格节点组装到班级表上
		//	班级表的node_ids为单元格id
		String[] nodeIds = courseTable.getNode_ids().split(",");
		List<CourseNode> nodeList = new ArrayList<>();	//	用来保存所有的单元格
		if(actualNodeList!=null && !actualNodeList.isEmpty()) {
			List<CourseNode> actualCourseNodeList = actualNodes2CourseNodes(actualNodeList);	//	实际的单元格节点
			for (String nodeId : nodeIds){
				CourseNode courseNode= courseNodeMapper.selectByPrimaryKey(nodeId);
				boolean flag = false;
				for (CourseNode actualCourseNode : actualCourseNodeList)  {
					//	实际节点中有
					if( actualCourseNode.getCourse_num() == courseNode.getCourse_num() && courseNode.getWeek().equals(actualCourseNode.getWeek())) {
						nodeList.add(actualCourseNode);
						flag = true;
						break;
					}
				}
				if(!flag) {	//	实际节点中没有
					//	不需要返回的设置为null
					courseNode.setId(null);
					courseNode.setYear_name(null);
					courseNode.setTerm_name(null);
					courseNode.setSchool_id(null);
					courseNode.setTeacher_id(null);
					courseNode.setCourse_table_id(null);
					courseNode.setWeek_type(null);
					nodeList.add(courseNode);
				}
			}
		}else {
			for (String nodeId : nodeIds){
				CourseNode courseNode= courseNodeMapper.selectByPrimaryKey(nodeId);
				//	不需要返回的设置为null
				courseNode.setId(null);
				courseNode.setYear_name(null);
				courseNode.setTerm_name(null);
				courseNode.setSchool_id(null);
				courseNode.setTeacher_id(null);
				courseNode.setCourse_table_id(null);
				courseNode.setWeek_type(null);
				nodeList.add(courseNode);
			}
		}
		
		courseTable.setNodeList(nodeList);
		courseTable.setId(null);
		courseTable.setNode_ids(null);
		
		return courseTable;
	}
	
	//	删除旧的课程表
	@SuppressWarnings("rawtypes")
	private void deleteOldCourseTable(String schoolId) {
		CourseTable courseTable = new CourseTable();
		courseTable.setSchool_id(schoolId);
		courseTableMapper.delete(courseTable);
		
		CourseNode courseNode = new CourseNode();
		courseNode.setSchool_id(schoolId);
		courseNodeMapper.delete(courseNode);
	}

	@Override
	public int queryFirstCount(String teacher_id, String week, int nodeIndex) {
		return courseNodeMapper.queryFirstCount(teacher_id,week,nodeIndex);
	}

	@SuppressWarnings("rawtypes")
	@Override
	public void saveUnmatchedTeacher(TeacherVo teacherVo) {
		//	更新tb_course_node 
		CourseNode courseNode = new CourseNode();
		courseNode.setTeacher_id(teacherVo.getTeacherId());
		Example nodeExample = new Example(CourseNode.class);
		Criteria nodeCriteria = nodeExample.createCriteria();
		nodeCriteria.andEqualTo("school_id",teacherVo.getSchoolId());
		nodeCriteria.andEqualTo("teacher_name",teacherVo.getTeacherName());
		courseNodeMapper.updateByExampleSelective(courseNode, nodeExample);
		
		//	更新tb_course_table
		CourseTable courseTable = new CourseTable();
		courseTable.setTeacherId(teacherVo.getTeacherId());;
		Example tableExample = new Example(CourseTable.class);
		Criteria tableCriteria = nodeExample.createCriteria();
		tableCriteria.andEqualTo("school_id",teacherVo.getSchoolId());
		tableCriteria.andEqualTo("teacher_name",teacherVo.getTeacherName());
		courseTableMapper.updateByExampleSelective(courseTable, tableExample);
	}

	@Override
	public Teacher queryTeacherByTeacherVo(TeacherVo teacherVo) {
		School school = schoolService.querySchoolById(teacherVo.getSchoolId());
		Teacher teacher = teacherService.queryTeacherByCellphoneAndSchoolName(teacherVo.getCellphone(),school.getName());
		return teacher;
	}
	
	@SuppressWarnings("rawtypes")
	@Override
	public int queryTotalCourseTable(String schoolId, String year, String term) {
		CourseTable table = new CourseTable();
		table.setSchool_id(schoolId);
		table.setTerm_name(term);
		table.setYear_name(year);
		table.setType(3);
		List<CourseTable> tableList = courseTableMapper.select(table);
		if(tableList==null || tableList.isEmpty()) {
			return 0;
		}
		return tableList.size();
	}
	///////////////////////////////////////////////////////


	@Override
	// 根据学校名称和教师名称计算该教师一周中的每一天各有几节课
	public Map<Integer, Integer> countNodeEveryDay(String schoolName, String teacherName) {

		Map<Integer, Integer> map = new HashMap<>();

		// 查询指定老师一周中的每天分别有多少节课
		int a = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期一");
		int b = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期二");
		int c = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期三");
		int d = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期四");
		int e = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期五");
		int f = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期六");
		int g = courseNodeMapper.queryTotalCourseByWeek(schoolName, teacherName, "星期日");

		map.put(1, a);
		map.put(2, b);
		map.put(3, c);
		map.put(4, d);
		map.put(5, e);
		map.put(6, f);
		map.put(7, g);

		return map;
	}


	@SuppressWarnings("rawtypes")
	@Override
	public List<CourseTable> queryTeacherBySchoolId(String schoolId, int type) {
		CourseTable record = new CourseTable<>();
		record.setSchool_id(schoolId);
		record.setType(type);
		return courseTableMapper.select(record);
	}

	@Override
	public List<CourseNode> queryKCBySchoolId(String schoolId) {
		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseNode> queryKCByTeacherNameAndSchoolId(String schoolId, String teacherName) {

		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setTeacher_name(teacherName);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseNode> queryTimeByTeacherNameAndKC(String schoolId, String teacherName, String kC) {
		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setTeacher_name(teacherName);
		record.setCourse_name(kC);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<Integer> queryCourseNumByTeacherNameAndKCAndWeek(String schoolId, String teacherName, String kC,
			String week) {
		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setTeacher_name(teacherName);
		record.setCourse_name(kC);
		record.setWeek(week);
		List<CourseNode> list = courseNodeMapper.select(record);
		List<Integer> list2 = new ArrayList<Integer>();
		for (CourseNode courseNode : list) {
			list2.add(courseNode.getCourse_num());
		}
		return list2;
	}

	@SuppressWarnings("rawtypes")
	@Override
	public CourseTable queryTeacherNameByTeacherId(String schoolId, String teacherId) {
		CourseTable record = new CourseTable<>();
		record.setTeacherId(teacherId);
		record.setSchool_id(schoolId);
		record.setType(2);
		CourseTable table = courseTableMapper.selectOne(record);// 从教师表中查询
		return table;
	}

	@Override
	public List<CourseNode> queryClassNameByTeacherNameAndKCAndRequestDay(String schoolId, String teacherId,
			String week) {
		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setTeacher_id(teacherId);
		record.setWeek(week);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseNode> queryKCByTeacherIdAndSchoolID(String teacherId, String schoolId) {

		CourseNode record = new CourseNode();
		record.setTeacher_id(teacherId);
		record.setSchool_id(schoolId);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseNode> queryCourseNumByTeacherNameAndKCAndRequestDayAndClassName(String teacherId, String schoolId,
			String requestDay, String className) {

		CourseNode record = new CourseNode();
		record.setTeacher_id(teacherId);
		record.setSchool_id(schoolId);
		record.setWeek(requestDay);
		record.setClass_name(className);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseNode> queryKCByTeacherIdAndSchoolIDAndRequestDayAndClassNameAndCourseNum(String teacherId,
			String schoolId, String week, String className, int courseNum) {

		CourseNode record = new CourseNode();
		record.setTeacher_id(teacherId);
		record.setSchool_id(schoolId);
		record.setWeek(week);
		record.setClass_name(className);
		record.setCourse_num(courseNum);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseNode> queryTeacherBySchoolIdAndRequestDayAndCourseNum(String schoolId, String requestDay,
			int courseNum) {
		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setWeek(requestDay);
		record.setCourse_num(courseNum);
		return courseNodeMapper.select(record);
	}

	@Override
	public HashSet<CourseNode> queryTeacherBySchoolIdAndClassName(String schoolId, String className) {

		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setClass_name(className);
		HashSet<CourseNode> set = new HashSet<>();
		List<CourseNode> list = courseNodeMapper.select(record);
		for (CourseNode courseNode : list) {
			set.add(courseNode);
		}
		return set;
	}

	@Override
	public CourseNode queryTeacherBySchoolIdAndRequestDayAndCourseNumAndClassNameAndGradeName(String schoolId,
			String requestDay, int courseNum, String className, String gradeName) {

		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setWeek(requestDay);
		record.setCourse_num(courseNum);
		record.setClass_name(className);
		record.setGrade_name(gradeName);
		return courseNodeMapper.selectOne(record);
	}

	@Override
	public HashSet<CourseNode> queryTeacherBySchoolIdAndGradeName(String schoolId, String gradeName) {

		CourseNode record = new CourseNode();
		record.setSchool_id(schoolId);
		record.setGrade_name(gradeName);
		List<CourseNode> list = courseNodeMapper.select(record);
		HashSet<CourseNode> set = new HashSet<>();
		for (CourseNode courseNode : list) {
			set.add(courseNode);
		}
		return set;
	}

	@Override
	public List<CourseTable> queryCourseTableBySchoolId(String school_id) {
		CourseTable record = new CourseTable<>();
		record.setSchool_id(school_id);
		return courseTableMapper.select(record);
	}

	@Override
	public CourseNode queryGradeNameBySchoolIdAndTeacherIdAndOldClassAndOldSubjectAndOld_nodeAndWeek(
			AdjustBill adjustBill) {

		CourseNode record = new CourseNode();
		record.setSchool_id(adjustBill.getSchool_id());
		record.setTeacher_id(adjustBill.getTeacher_id());
		record.setClass_name(adjustBill.getOld_class());
		record.setCourse_name(adjustBill.getOld_subject());
		record.setCourse_num(adjustBill.getOld_node());
		record.setWeek(adjustBill.getWeek());
		return courseNodeMapper.selectOne(record);
	}

	@Override
	public List<CourseTable> queryCourseTableByClassNameAndSchoolNameAndGradeName(String className, String schoolId,
			String gradeName) {
		CourseTable record = new CourseTable<>();
		record.setSchool_id(schoolId);
		record.setClass_name(className);
		record.setGrade_name(gradeName);
		record.setType(1);
		return courseTableMapper.select(record);
	}

	@Override
	public CourseNode queryCourseNodeByCourseNodeId(String courseNodeId) {
		return courseNodeMapper.selectByPrimaryKey(courseNodeId);
	}

	@Override
	public List<CourseTable> queryCourseTableBySchoolIdAndTeacherId(String schoolId, String teacherId) {
		CourseTable record = new CourseTable<>();
		record.setSchool_id(schoolId);
		record.setTeacherId(teacherId);
		return courseTableMapper.select(record);
	}

	@Override
	public List<CourseNode> queryClassNameByTeacherNameAndKCAndRequestDayAndCourseNum(String school_id,
			String petitioner_id, String week, Integer course_num) {
		CourseNode record=new CourseNode();
		record.setSchool_id(school_id);
		record.setTeacher_id(petitioner_id);
		record.setWeek(week);
		record.setCourse_num(course_num);
		return courseNodeMapper.select(record);
	}

	@Override
	public List<CourseTable> queryCourseTableBySchoolIdAndTeacherId2(String schoolId, String teacherId, String string) {
		CourseTable record=new CourseTable<>();
		record.setSchool_id(schoolId);
		record.setTeacherId(teacherId);
		record.setType(2);
		return courseTableMapper.select(record);
	}

	@Override
	public List<CourseNode> queryCourseNodeByTeacherId(String school_id, String petitioner_id) {
		CourseNode record=new CourseNode();
		record.setSchool_id(school_id);
		record.setTeacher_id(petitioner_id);
		return courseNodeMapper.select(record);
	}

	@Override
	public int queryCountBetween(String teacher_id, String week, int startNode, int endNode) {
		return courseNodeMapper.queryCountBetween(teacher_id, week, startNode, endNode);
	}




}
